package dao;

import bean.Class;
import bean.User;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import util.DruidUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @Author : liuchangling
 * @Descrition :
 * @Date： Created in 2:19 下午 2021/6/4
 */
public class ClassDaoImp implements ClassDao {

    @Override
    public int addClass(Class e) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "insert into classes(classNo,className,gradeNo,teacherName,classSlogan,classNum) values(?,?,?,?,?,?)";
        Object[] param = {e.getClassNo(), e.getClassName(), e.getGradeNo(), e.getTeacherName(), e.getClassSlogan(), e.getClassNum()};
        Connection connection = DruidUtils.getConnection();
        int i = 0;
        try {
            i = queryRunner.update(connection, sql, param);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        DbUtils.closeQuietly(connection);
        return i;
    }

    @Override
    public int updateClass(Class e) {
        int i = 0;
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "update classes set className=?,teacherName=?,classSlogan=?,classNum=? where classNo=?";
        Object[] param={e.getClassName(),e.getTeacherName(),e.getClassSlogan(),e.getClassNum(),e.getClassNo()};
        try {
            i=queryRunner.update(sql,param);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return i;
    }

    @Override
    public int deleteClass(int classNo) {
        int i = 0;
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "delete from classes where classNo=?";
        try {
            i = queryRunner.update(sql, classNo);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public List<Class> selectClass(int classNo) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from classes where classNo=?";
        try {
            List<Class> list = queryRunner.query(sql, new BeanListHandler<>(Class.class), classNo);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Class> selectAllClass(int page) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from classes limit ?,?";
        try {
            List<Class> list = queryRunner.query(sql, new BeanListHandler<>(Class.class), page,page+2);
            System.out.println(page);
            System.out.println(list);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public String selectUserUnionClass(int classNo) {
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
        String sql="select count(sno) from user inner join classes on user.classNo=classes.classNo where user.classNo=?";
        try {
            Object[] query = queryRunner.query(sql, new ArrayHandler(), classNo);
            return query[0].toString();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
